using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace Test {
	class RfStats {
		static void MainX(string[] args) {
			SQL2 local=new SQL2("server=(local);database=repotest;integrated security=sspi");
			//local.NonQry("truncate table RfStat");
			Dictionary<string,string> site=new Dictionary<string,string>();
			site.Add("ElmWay","server=pon-wpsq21-db02\\in02;database=rf;integrated security=sspi");
			site.Add("Depot","server=pon-wpsq21-db10\\in10;database=rf;integrated security=sspi");
			site.Add("ElmDrive","server=pon-wpsq21-db03\\in03;database=rf2;integrated security=sspi");
			site.Add("Doncaster","server=pon-wpsq21-db06\\in06;database=rf;integrated security=sspi");
			site.Add("DVP","server=pon-wpsq21-db07\\in07;database=rf;integrated security=sspi");
			site.Add("Stadium","server=PON-WPSQ21-DB08\\IN08;database=rf;integrated security=sspi");
			string sql=@"select @@servername srv,convert(char(10),ChangeTime,20)[hdate],parsename(address,4)+'.'+parsename(address,3)+'.'+parsename(address,2)[Network],fromapplication,count(*)[cnt],count(distinct address)[dip],count(distinct bonus)[dbns]
from rfStateChangeBonusLog(nolock)where datediff(day,ChangeTime,getdate())=1
group by fromapplication,parsename(address,4)+'.'+parsename(address,3)+'.'+parsename(address,2),convert(char(10),ChangeTime,20)";
			StringBuilder sb=new StringBuilder();
			foreach(KeyValuePair<string,string> dict in site)
			{
				SQL2 rem=new SQL2(dict.Value);
				DataTable tab=rem.Query(sql);
				if(tab.Rows.Count>0)
					foreach(DataRow row in tab.Rows)
						sb.AppendFormat("insert into RfDStat(hdate,network,app,cnt,dip,dbns,whs,srv)values(convert(date,'{0}'),'{1}','{2}',{3},{4},{5},'{6}','{7}'){8}",row["hdate"],row["Network"],row["fromapplication"].ToString().Replace("'",""),row["cnt"],row["dip"],row["dbns"],dict.Key,row["srv"],Environment.NewLine);
			}
			local.NonQry(sb.ToString());
		}
	}
	class SQL2 {
		string _cs;
		public SQL2() : this("server=localhost;integrated security=sspi;") { }
		public SQL2(params string[] args) {
			if(args.Length==2)
				_cs=string.Format("server={0};database={1};integrated security=sspi;",args);
			else
				throw new Exception();
		}
		public SQL2(string cs) { _cs=cs; }


		public DataTable Query(string sql,params object[] args) {
			string qry=string.Format(sql,args);
			DataTable ret=new DataTable();
			using(SqlDataAdapter adp=new SqlDataAdapter(qry,_cs))
			{
				adp.SelectCommand.CommandTimeout=0;
				adp.Fill(ret);
			}
			return ret;
		}
		public int NonQry(string sql) {
			int ret=-1;
			using(SqlConnection con=new SqlConnection(_cs))
			using(SqlCommand cmd=new SqlCommand(sql,con))
			{
				con.Open();
				ret=cmd.ExecuteNonQuery();
				con.Close();
			}
			return ret;
		}
	}
}
/*
drop table RfStat
create table RfStat(
id int identity primary key,
hdate datetime,
network varchar(15),
whs varchar(15),
app varchar(50),
cnt int,
dip int,
dbns int,
inserted datetime default getdate()
)
create table RfNetwork(
id int identity primary key,
network varchar(15),
site varchar(20)
)
drop table RfDStat
create table RfDStat(
id int identity primary key,
hdate date,
network varchar(15),
whs varchar(15),
app varchar(50),
cnt int,
dip int,
dbns int,
inserted datetime default getdate()
)
*/